{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Musicians - Easy\n",
    "\n",
    "## Tables used in the musicians database\n",
    "\n",
    "band(**band_no**, band_name, band_home, band_type, b_date, band_contact)\n",
    "\n",
    "composer(**comp_no**, comp_is, comp_type)\n",
    "\n",
    "composition(**c_no**, comp_date, c_title, c_in)\n",
    "\n",
    "concert(**concert_no**, concert_venue, concert_in, con_date, concert_orgniser)\n",
    "\n",
    "has_composed(**_cmpr_no_**, **_cmpn_no_**)\n",
    "\n",
    "musician(**m_no**, m_name, born, died, born_in, living_in)\n",
    "\n",
    "performance(**_pfrmnc_no_**, gave, performed, conducted_by, performed_in)\n",
    "\n",
    "performer(**perf_no**, perf_is, instrument, perf_type)\n",
    "\n",
    "place(**place_no**, place_town, place_country)\n",
    "\n",
    "plays_in(**_player_**, **band_id**)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- **musician**\n",
    "\n",
    "m_no | m_name | born | died | born_in | living_in\n",
    "-:|-----------|------|------|--------:|-----:\n",
    "1 | Fred Bloggs | 02/01/48 |  | 1 | 2\n",
    "2 | John Smith | 03/03/50 |  | 3 | 4\n",
    "3 | Helen Smyth | 08/08/48 |  | 4 | 5\n",
    "4 | Harriet Smithson | 09/05/1909 | 20/09/80 | 5 | 6\n",
    "5 | James First | 10/06/65 |  | 7 | 7\n",
    "... | | | | | \n",
    "\n",
    "- **place**\n",
    "\n",
    "place_no | place_town | place_country\n",
    "--------:|------------|---------\n",
    "1 | Manchester | England\n",
    "2 | Edinburgh | Scotland\n",
    "3 | Salzburg | Austria\n",
    "4 | New York | USA\n",
    "5 | Birmingham | England\n",
    "... | | \n",
    "\n",
    "- **performer**\n",
    "\n",
    "perf_no | perf_is | instrument | perf_type\n",
    "-:|--:|--------|---------------------\n",
    "1 | 2 | violin | classical\n",
    "2 | 4 | viola | classical\n",
    "3 | 6 | banjo | jazz\n",
    "4 | 8 | violin | classical\n",
    "5 | 12 | guitar | jazz\n",
    "...| | |\n",
    "\n",
    "- **composer**\n",
    "\n",
    "comp_no | comp_is | comp_type\n",
    "-:|--:|----------------\n",
    "1 | 1 | jazz\n",
    "2 | 3 | classical\n",
    "3 | 5 | jazz\n",
    "4 | 7 | classical\n",
    "5 | 9 | jazz\n",
    "... | |\n",
    "\n",
    "- **band**\n",
    "\n",
    "band_no | band_name | band_home | band_type | b_date | band_contact\n",
    "-:|-----|--:|-----------|----------|----:\n",
    "1 | ROP | 5 | classical | 30/01/01 | 11\n",
    "2 | AASO | 6 | classical |  | 10\n",
    "3 | The J Bs | 8 | jazz |  | 12\n",
    "4 | BBSO | 9 | classical |  | 21\n",
    "5 | The left Overs | 2 | jazz |  | 8\n",
    "... | | | | |\n",
    "\n",
    "- **plays_in**\n",
    "\n",
    "player | band_id\n",
    "-:|---:\n",
    "1 | 1\n",
    "1 | 7\n",
    "3 | 1\n",
    "4 | 1\n",
    "4 | 7\n",
    "... |\n",
    "\n",
    "- **composition**\n",
    "\n",
    "c_no | comp_date | c_title | c_in\n",
    "-:|----------|--------|-------:\n",
    "1 | 17/06/75 | Opus 1 | 1\n",
    "2 | 21/07/76 | Here Goes | 2\n",
    "3 | 14/12/81 | Valiant Knight | 3\n",
    "4 | 12/01/82 | Little Piece | 4\n",
    "5 | 13/03/85 | Simple Song | 5\n",
    "... | | |\n",
    "\n",
    "- **has_composed**\n",
    "\n",
    "cmpr_no | cmpn_no\n",
    "-:|---:\n",
    "1 | 1\n",
    "1 | 8\n",
    "2 | 11\n",
    "3 | 2\n",
    "3 | 13\n",
    "... |\n",
    "\n",
    "- **concert**\n",
    "\n",
    "concert_no | concert_venue | concert_in | con_date | concert_orgniser\n",
    "-:|------------------|--:|----------|----:\n",
    "1 | Bridgewater Hall | 1 | 06/01/95 | 21\n",
    "2 | Bridgewater Hall | 1 | 08/05/96 | 3\n",
    "3 | Usher Hall | 2 | 03/06/95 | 3\n",
    "4 | Assembly Rooms | 2 | 20/09/97 | 21\n",
    "5 | Festspiel Haus | 3 | 21/02/95 | 8\n",
    "... | | | |\n",
    "\n",
    "- **performance**\n",
    "\n",
    "pfrmnc_no | gave | performed | conducted_by | performed_in\n",
    "-:|--:|--:|---:|-----------------------:\n",
    "1 | 1 | 1 | 21 | 1\n",
    "2 | 1 | 3 | 21 | 1\n",
    "3 | 1 | 5 | 21 | 1\n",
    "4 | 1 | 2 | 1 | 2\n",
    "5 | 2 | 4 | 21 | 2\n",
    "... | | | |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here is the data model of the musician tables\n",
    "\n",
    "![](../src/img/musician_str.svg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      " ········\n"
     ]
    }
   ],
   "source": [
    "import getpass\n",
    "import psycopg2\n",
    "from sqlalchemy import create_engine\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "pwd = getpass.getpass()\n",
    "engine = create_engine(\n",
    "    'postgresql+psycopg2://postgres:%s@192.168.31.31:15432/sqlzoo' % (pwd))\n",
    "pd.set_option('display.max_rows', 60)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "composer = pd.read_sql_table('composer', engine)\n",
    "composition = pd.read_sql_table('composition', engine)\n",
    "concert = pd.read_sql_table('concert', engine)\n",
    "has_composed = pd.read_sql_table('has_composed', engine)\n",
    "musician = pd.read_sql_table('musician', engine)\n",
    "performance = pd.read_sql_table('performance', engine)\n",
    "performer = pd.read_sql_table('performer', engine)\n",
    "place = pd.read_sql_table('place', engine)\n",
    "plays_in = pd.read_sql_table('plays_in', engine)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.\n",
    "**Give the organiser's name of the concert in the Assembly Rooms after the first of Feb, 1997.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>m_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>James Steeple</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          m_name\n",
       "0  James Steeple"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(concert.loc[(concert['concert_venue']=='Assembly Rooms') & \n",
    "             (concert['con_date']>'1997-02-01')]\n",
    " .merge(musician, left_on='concert_orgniser', right_on='m_no')\n",
    " [['m_name']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.\n",
    "**Find all the performers who played guitar or violin and were born in England.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>m_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Alan Fluff</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Davis Heavan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Harriet Smithson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Harry Forte</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>James First</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Theo Mengel</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             m_name\n",
       "3        Alan Fluff\n",
       "1      Davis Heavan\n",
       "2  Harriet Smithson\n",
       "0       Harry Forte\n",
       "5       James First\n",
       "4       Theo Mengel"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(performer.loc[performer['instrument'].isin(['violin', 'guitar'])]\n",
    " .merge(musician, left_on='perf_is', right_on='m_no')\n",
    " .merge(place.loc[place['place_country']=='England'],\n",
    "        left_on='born_in', right_on='place_no')\n",
    " [['m_name']].sort_values('m_name'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.\n",
    "**List the names of musicians who have conducted concerts in USA together with the towns and dates of these concerts.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>m_name</th>\n",
       "      <th>place_town</th>\n",
       "      <th>con_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>James Steeple</td>\n",
       "      <td>New York</td>\n",
       "      <td>1997-06-15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          m_name place_town   con_date\n",
       "0  James Steeple   New York 1997-06-15"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(concert.merge(place.loc[place['place_country']=='USA'],\n",
    "               left_on='concert_in', right_on='place_no')\n",
    " .merge(performance, left_on='concert_no', right_on='performed_in')\n",
    " .merge(musician, left_on='conducted_by', right_on='m_no')\n",
    " [['m_name', 'place_town', 'con_date']].drop_duplicates()\n",
    " .sort_values('m_name'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.\n",
    "**How many concerts have featured at least one composition by Andy Jones? List concert date, venue and the composition's title.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>con_date</th>\n",
       "      <th>concert_venue</th>\n",
       "      <th>c_title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1996-05-08</td>\n",
       "      <td>Bridgewater Hall</td>\n",
       "      <td>A Simple Piece</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    con_date     concert_venue         c_title\n",
       "0 1996-05-08  Bridgewater Hall  A Simple Piece"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(concert.merge(composition, left_on='concert_no', right_on='c_in')\n",
    " .merge(has_composed, left_on='c_no', right_on='cmpn_no')\n",
    " .merge(composer, left_on='cmpr_no', right_on='comp_no')\n",
    " .merge(musician.loc[musician['m_name']=='Andy Jones'], \n",
    "        left_on='comp_is', right_on='m_no')\n",
    " [['con_date', 'concert_venue', 'c_title']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.\n",
    "**List the different instruments played by the musicians and avg number of musicians who play the instrument.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>m_name</th>\n",
       "      <th>Alan Fluff</th>\n",
       "      <th>Davis Heavan</th>\n",
       "      <th>Elsie James</th>\n",
       "      <th>Harriet Smithson</th>\n",
       "      <th>Harry Forte</th>\n",
       "      <th>Helen Smyth</th>\n",
       "      <th>James First</th>\n",
       "      <th>James Quick</th>\n",
       "      <th>Jeff Dawn</th>\n",
       "      <th>John Smith</th>\n",
       "      <th>Louise Simpson</th>\n",
       "      <th>Sue Little</th>\n",
       "      <th>Theo Mengel</th>\n",
       "      <th>All</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>instrument</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>banjo</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bass</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cello</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>clarinet</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cornet</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>drums</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>flute</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>guitar</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>horn</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trombone</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trumpet</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>viola</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>violin</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "m_name      Alan Fluff  Davis Heavan  Elsie James  Harriet Smithson  \\\n",
       "instrument                                                            \n",
       "banjo                0             0            0                 0   \n",
       "bass                 0             0            0                 0   \n",
       "cello                0             1            1                 0   \n",
       "clarinet             0             0            0                 0   \n",
       "cornet               0             0            0                 0   \n",
       "drums                0             0            0                 0   \n",
       "flute                0             0            0                 0   \n",
       "guitar               0             1            1                 0   \n",
       "horn                 0             0            0                 0   \n",
       "trombone             0             0            0                 0   \n",
       "trumpet              0             0            0                 0   \n",
       "viola                1             1            1                 1   \n",
       "violin               1             0            0                 1   \n",
       "All                  2             3            3                 2   \n",
       "\n",
       "m_name      Harry Forte  Helen Smyth  James First  James Quick  Jeff Dawn  \\\n",
       "instrument                                                                  \n",
       "banjo                 0            0            0            0          0   \n",
       "bass                  0            0            0            0          1   \n",
       "cello                 0            0            0            0          0   \n",
       "clarinet              1            0            0            0          0   \n",
       "cornet                0            0            0            0          0   \n",
       "drums                 1            0            0            0          0   \n",
       "flute                 0            0            0            1          0   \n",
       "guitar                0            0            0            0          0   \n",
       "horn                  0            1            0            0          0   \n",
       "trombone              0            0            0            0          0   \n",
       "trumpet               0            0            0            1          0   \n",
       "viola                 0            0            0            0          0   \n",
       "violin                1            0            1            0          1   \n",
       "All                   3            1            1            2          2   \n",
       "\n",
       "m_name      John Smith  Louise Simpson  Sue Little  Theo Mengel  All  \n",
       "instrument                                                            \n",
       "banjo                0               0           0            1    1  \n",
       "bass                 1               1           0            0    3  \n",
       "cello                0               0           1            0    3  \n",
       "clarinet             0               0           0            0    1  \n",
       "cornet               0               1           0            0    1  \n",
       "drums                0               0           0            1    2  \n",
       "flute                1               0           0            0    2  \n",
       "guitar               0               0           0            0    2  \n",
       "horn                 0               0           0            0    1  \n",
       "trombone             0               1           0            0    1  \n",
       "trumpet              0               0           0            0    1  \n",
       "viola                0               0           0            0    4  \n",
       "violin               1               0           0            1    7  \n",
       "All                  3               3           1            3   29  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = (performer.merge(musician, left_on='perf_is', right_on='m_no')\n",
    "     .pivot_table(columns='m_name', index='instrument', values='m_no', \n",
    "                  aggfunc='count', fill_value=0, margins=True))\n",
    "a"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
